﻿-- Modification aux vues et stores procs pour enlever les colonnes qui ont été retirées.
DECLARE @alter NVARCHAR(MAX)
	
	
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_medianamik_NodeByPath]'))
BEGIN
	SELECT @alter = '
ALTER VIEW [dbo].[vw_medianamik_NodeByPath]
AS
SELECT     Node.NodeId, Node.ParentNodeId, Node.TypeId, Node.CreatedOn, Node.CreatedBy, Node.ModifiedOn, Node.ModifiedBy, Node.Flags, 
                      Node.IsDeleted, Node.Path, Node.Depth, Node.Position, Node.EditedOn, Node.EditedBy
, CASE WHEN BaseNode.NodeId = Node.NodeId THEN NULL ELSE BaseNode.NodeId END  AS BaseNodeId
, CASE WHEN BaseType.TypeId = NodeType.TypeId THEN NULL ELSE BaseType.TypeId END AS BaseTypeId
FROM         dbo.medianamik_Node AS Node INNER JOIN
                      dbo.medianamik_Type AS NodeType ON Node.TypeId = NodeType.TypeId LEFT OUTER JOIN
                      dbo.medianamik_Node AS BaseNode ON Node.Path LIKE BaseNode.Path + ''%'' LEFT OUTER JOIN
                      dbo.medianamik_Type AS BaseType ON NodeType.Path LIKE BaseType.Path + ''%''

'

	EXEC sp_executesql @alter
END

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_medianamik_BaseContentNode]'))
BEGIN
	SELECT @alter = '
ALTER VIEW [dbo].[vw_medianamik_BaseContentNode]
AS

Select n.NodeId, n.ParentNodeId, n.TypeId, n.CreatedOn, n.CreatedBy, n.ModifiedOn, n.ModifiedBy, n.Flags, n.IsDeleted, n.Path, n.Depth, n.Position, n.EditedOn, n.EditedBy
,ni.IsActive, ni.CultureName
, Name.Value, Name.Revision
From medianamik_Node n 
Inner join Medianamik_NodeInstance ni On ni.NodeId = n.NodeId
Inner join Medianamik_NodePropertyValue Name on Name.NodeId = n.NodeId And Name.CultureName = ni.CultureName
Where n.IsDeleted = 0
And NAme.PropertyId =''35A3F17A-A4C8-4331-9E8F-10FC27C18445'' 

'

	EXEC sp_executesql @alter
END


IF  EXISTS (SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID(N'[dbo].[medianamik_GetChildNodes]'))
BEGIN
	SELECT @alter = '
-- =============================================
-- Author:		Maxime
-- Create date: 27 mai 2008
-- Description:	
-- =============================================
ALTER PROCEDURE [dbo].[medianamik_GetChildNodes] 
	-- Add the parameters for the stored procedure here
	@ParentId uniqueidentifier,
	@Inclusivity varchar(5) = ''_%'' --Non inclusive, % = inclusive
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT Node.NodeId, Node.TypeId, Node.ParentNodeId, Node.[Path], Node.Depth, Node.Position, Node.EditedOn, Node.EditedBy, Node.CreatedOn
	FROM medianamik_Node as Node
	WHERE Node.IsDeleted = 0
	AND [Path] LIKE (SELECT [Path]
	FROM medianamik_Node
	WHERE NodeId = @ParentId) + @Inclusivity
	ORDER BY [Path], Position
END
'

	EXEC sp_executesql @alter
END

IF  EXISTS (SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID(N'[dbo].[medianamik_GetRootNodesByType]'))
BEGIN
	SELECT @alter = '
-- =============================================
-- Author:		Maxime
-- Create date: 16 juillet 2008
-- Description:	
-- =============================================
ALTER PROCEDURE [dbo].[medianamik_GetRootNodesByType]
	-- Add the parameters for the stored procedure here
	@TypeId uniqueidentifier
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT Node.NodeId, Node.TypeId, Node.ParentNodeId, Node.[Path], Node.Depth, Node.Position, Node.EditedOn, Node.EditedBy, Node.CreatedOn
	FROM medianamik_Node as Node
	INNER JOIN medianamik_Type as [Type] ON Node.TypeId = [Type].TypeId
	WHERE Node.IsDeleted = 0
	AND Node.ParentNodeId is null
	AND [Type].Path LIKE (SELECT [Path]
	FROM medianamik_Type
	WHERE TypeId = @TypeId) + ''%''
	ORDER BY Node.[Path], Node.Position
END

'

	EXEC sp_executesql @alter
END


IF  EXISTS (SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID(N'[dbo].[medianamik_GetChildNodesFilteredByType]'))
BEGIN
	SELECT @alter = '
-- =============================================
-- Author:		Maxime
-- Create date: 28 mai 2008
-- Description:	
-- =============================================
ALTER PROCEDURE [dbo].[medianamik_GetChildNodesFilteredByType]
	-- Add the parameters for the stored procedure here
	@ParentId uniqueidentifier,
	@Inclusivity varchar(5) = ''_%'', --Non inclusive, % = inclusive
	@TypeId uniqueidentifier
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT OFF;

    -- Insert statements for procedure here
	SELECT Node.NodeId, Node.TypeId, Node.ParentNodeId, Node.[Path], Node.Depth, Node.Position, Node.EditedOn, Node.EditedBy, Node.CreatedOn
	FROM medianamik_Node as Node
	INNER JOIN medianamik_Type as [Type] ON Node.TypeId = [Type].TypeId
	WHERE Node.IsDeleted = 0
	AND Node.[Path] LIKE (SELECT [Path]
	FROM medianamik_Node
	WHERE NodeId = @ParentId) + @Inclusivity
	AND [Type].Path LIKE (SELECT [Path]
	FROM medianamik_Type
	WHERE TypeId = @TypeId) + ''%''
	ORDER BY Node.[Path], Node.Position
END
'

	EXEC sp_executesql @alter
END


IF  EXISTS (SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID(N'[dbo].[medianamik_GetSomeChildNodesFilteredByType]'))
BEGIN
	SELECT @alter = '
-- =============================================
-- Author:		Maxime
-- Create date: 28 mai 2008
-- Description:	
-- =============================================
ALTER PROCEDURE [dbo].[medianamik_GetSomeChildNodesFilteredByType]
	-- Add the parameters for the stored procedure here
	@ParentId uniqueidentifier,
	@Inclusivity varchar(5) = ''_%'', --Non inclusive, % = inclusive
	@TypeId uniqueidentifier,
	@Depth int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT Node.NodeId, Node.TypeId, Node.ParentNodeId, Node.[Path], Node.Depth, node.Position, Node.EditedOn, Node.EditedBy, Node.CreatedOn
	FROM medianamik_Node as Node
	INNER JOIN medianamik_Type as [Type] ON Node.TypeId = [Type].TypeId
	WHERE Node.IsDeleted = 0
	AND Node.[Path] LIKE (SELECT [Path]
	FROM medianamik_Node
	WHERE NodeId = @ParentId) + @Inclusivity
	AND [Type].Path LIKE (SELECT [Path]
	FROM medianamik_Type
	WHERE TypeId = @TypeId) + ''%''
	AND Node.Depth - (SELECT Depth
	FROM medianamik_Node
	WHERE NodeId = @ParentId) <= @Depth
	ORDER BY Node.[Path], Node.Position
END
'

	EXEC sp_executesql @alter
END



IF  EXISTS (SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID(N'[dbo].[medianamik_GetNodesByType]'))
BEGIN
	SELECT @alter = '
-- =============================================
-- Author:		Maxime
-- Create date: 28 mai 2008
-- Description:	
-- =============================================
ALTER PROCEDURE [dbo].[medianamik_GetNodesByType]
	-- Add the parameters for the stored procedure here
	@TypeId uniqueidentifier
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT Node.NodeId, Node.TypeId, Node.ParentNodeId, Node.[Path], Node.Depth, Node.Position, Node.EditedOn, Node.EditedBy, Node.CreatedOn
	FROM medianamik_Node as Node
	INNER JOIN medianamik_Type as [Type] ON Node.TypeId = [Type].TypeId
	WHERE Node.IsDeleted = 0
	AND [Type].Path LIKE (SELECT [Path]
	FROM medianamik_Type
	WHERE TypeId = @TypeId) + ''%''
	ORDER BY Node.[Path], Node.Position
END
'

EXEC sp_executesql @alter
END
